--#### 变量
select * from AccountInfo;
select *from BankCard;
select * from CardExchange;
select * from CardStateChange;
select * from CardTransfer;
--1. 为赵云此人进行开户开卡操作，赵云身份证：420107199904054233
declare @idcard varchar(20),@a varchar(20),@aid int,@cn varchar(30)
set @idcard = '420107199904054233'
select @a=AccountCode from AccountInfo where AccountCode=@idcard

if(@a is null)
 begin
 insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime) values (@idcard,'           ','赵云',GETDATE())
 select @aid =AccountId  from AccountInfo where AccountCode=@idcard
 insert into BankCard  (CardNo,AccountId,CardPwd,CardMoney,CardState,CardTime) values ('6225547858741264',@aid,'123456',0,1,GETDATE ())
 select @cn=CardNo from BankCard where AccountId=@aid
print '您已成功开卡开户，你的卡号是' +@cn+'初始密码是123456'
 end
 else
 begin
 print '已有此用户'
 end

--2. 需要求出张飞的银行卡卡号和余额，张飞身份证：420107199602034138  （1.使用连接查询，2.使用变量）

--#### 逻辑控制

--##### 条件分支if-else
   

--?	3.某用户银行卡号为“6225547854125656”，该用户执行取钱操作，取钱5000元，余额充足则进行取钱操作，并提示"取钱成功"，否则提示“余额不足”。

--##### 条件分支:case-when
declare @cardmoney money;
select @cardmoney= CardMoney from BankCard where CardNo='6225547854125656';
if @cardmoney>5000 
begin
update BankCard set CardMoney=CardMoney-5000 where CardNo='6225547854125656' 
insert into CardExchange (CardNo,MoneyInBank,MoneyOutBank,ExchangeTime) values ('6225547854125656',0,5000,GETDATE())
print '取钱成功'
end
else
begin
print '余额不足'
end

--?	4.查询银行卡信息，将银行卡状态1,2,3,4分别转换为汉字“正常,挂失,冻结,注销”，并且根据银行卡余额显示银行卡等级 30万以下为“普通用户”,30万及以上为"VIP用户"，  

----显示列分别为卡号，身份证，姓名，余额，用户等级，银行卡状态。

--##### **while**
select CardNo,AccountCode,RealName,CardMoney ,
(
case 
when CardState=1 then '正常'
when CardState=2 then '挂失'
when CardState=3 then '冻结'
when CardState=4 then '注销'
end 
) 银行卡状态,
(
case 
when CardMoney>30000 then 'VIP客户'
when CardMoney<30000 then '普通用户'
end 
) 银行卡等级
  from BankCard
  inner join AccountInfo on AccountInfo.AccountId=BankCard.AccountId

--?	5.循环打印1-10。
declare @b int 
set @b=1
while (@b<=10)
begin
print @b
set @b+=1
end
----?	6.打印99乘法表
declare @n int = 1
while @n<=9
begin

	declare @m int = 1
	declare @result varchar(500) = ''
	while @m<=@n

	begin
	set @result = @result + convert(varchar(2), @n) + ' * ' + 
	convert(varchar(2), @m) + ' = '+ convert(varchar(2),@n*@m) + char(9)
	set @m =@m + 1	
	end

	print @result
	set @n = @n + 1

end